In this blog, I’m explaining user defined functions in sql server.
There are three Types of User Defined Functions in Sql Server:
· Scalar
· Inline Table-Valued
· Multi-statement Table-Valued
Scalar User Defined Functions
The scalar user defined functions can accept zero to many parameters and will return a single scalar value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc) data types.
Example
CREATE FUNCTION AddTwoNumbers
(
@num1 int,
@num2 int
)
RETURNS int
AS
BEGIN
RETURN @num1 + @num2
END
The above function AddTwoNumbers will accept two numbers, add them and give the result.
You can use either of the two statements to call the fumtion.
PRINT dbo.AddTwoNumbers(10,20)
SELECT dbo.AddTwoNumbers(30,20)
Output
Inline Table-Valued User Defined Function
An inline table-valued function returns a variable of data type table whose
value is derived from a single SELECT statement. Since the return value is
derived from the SELECT statement, there is no BEGIN/END block needed
in the CREATE FUNCTION statement.
Example
CREATE FUNCTION GetEMPByDept
(
@dept varchar(10)
)
RETURNS table
AS
RETURN(SELECT * FROM EMP where DEPT=@dept)
GO
The above function GetEMPByDept will accept one parameter @dept and gives the output in tabular format.
(
@dept varchar(10)
)
RETURNS table
AS
RETURN(SELECT * FROM EMP where DEPT=@dept)
GO
The above function GetEMPByDept will accept one parameter @dept and gives the output in tabular format.
Call the function using this statement
SELECT * FROM GetEMPByDept('Testing')
Output
Multi-Statement Table-Valued User Defined Function
A Multi-Statement Table-Valued user-defined function returns a table. It
can have one or more than one T-Sql statement. Within the create function
command you must define the table structure that is being returned.
Example
CREATE FUNCTION GetDeptByID
( @id int )
RETURNS
@DEPT table (
ID int,
DEPT Varchar(20)
)
AS
BEGIN
INSERT INTO @DEPT SELECT EMPID,DEPT FROM EMP WHERE EMPID = @id
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @DEPT VALUES ('','No Authors Found')
END
RETURN
END
GO
( @id int )
RETURNS
@DEPT table (
ID int,
DEPT Varchar(20)
)
AS
BEGIN
INSERT INTO @DEPT SELECT EMPID,DEPT FROM EMP WHERE EMPID = @id
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @DEPT VALUES ('','No Authors Found')
END
RETURN
END
GO
The above function GetDeptByIDwill accept one parameter @id and gives
the output in table DEPT.
Call the function using this statement
SELECT * FROM GetDeptByID(2)
Leave Comment